﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace HuaCheng.DataAccess
{
   /// <summary>
   /// 对设备表和通道映射表的数据库操作
   /// </summary>
    public class ChlMapHelper
    {
        /// <summary>
        /// 获取设备映射表和并联表的联合查询
        /// </summary>
        /// <param name="unitId"></param>
        /// <returns></returns>
        public static List<BTSChlMap> GetChlPalallelInfo(string ip,int gid,int mid)
        {
            List<BTSChlMap> chls = new List<BTSChlMap>();
            try
            {
                string sqlStr = "select bts_chl_map.*,muti_chl.parallel_id from bts_chl_map left join muti_chl" +
                                    " on bts_chl_map.chl_map_id = muti_chl.chl_id and bts_chl_map.dev_ip = muti_chl.dev_ip"+
                                    " and bts_chl_map.cabinet_id = muti_chl.cabinet_id and bts_chl_map.mid_id = muti_chl.mid_id"+
                                    "  where bts_chl_map.dev_ip=@ip and bts_chl_map.cabinet_id=@gid and bts_chl_map.mid_id=@mid";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            BTSChlMap chl = new BTSChlMap()
                            {
                                DevIp = Convert.ToString(reader["dev_ip"]),
                                Mid = Convert.ToInt32(reader["mid_id"]),
                                Gid = Convert.ToInt32(reader["cabinet_id"]),
                                ChlMapId = Convert.ToInt32(reader["chl_map_id"]),
                                ChlRealId = Convert.ToInt32(reader["real_chl_id"]),
                                DevParallelGroup = Convert.ToString(reader["parallel_id"]) == "" ? -1 : Convert.ToInt32(reader["parallel_id"]),
                                RevocationStatus = Convert.ToBoolean(reader["revo_stat"]),
                            };
                            chls.Add(chl);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return chls;
        }
        /// <summary>
        /// 查询指定设备的通道排布情况
        /// </summary>
        /// <param name="uid"></param>
        public static List<BTSChlMap> GetDevChlMap(string ip,int gid,int mid)
        {
            List<BTSChlMap> chls = new List<BTSChlMap>();
            try
            {
                string sqlStr = "select * from bts_chl_map where dev_ip = @ip and cabinet_id=@gid and mid_id=@mid";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            BTSChlMap chl = new BTSChlMap()
                            {
                                Gid = Convert.ToInt32(reader["cabinet_id"]),
                                Mid = Convert.ToInt32(reader["mid_id"]),
                                ChlMapId = Convert.ToInt32(reader["chl_map_id"]),
                                ChlRealId = Convert.ToInt32(reader["real_chl_id"]),
                                RevocationStatus = Convert.ToBoolean(reader["revo_stat"])
                            };
                            chls.Add(chl);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return chls;
        }
        /// <summary>
        /// 增加/修改通道映射表
        /// </summary>
        /// <param name="bts"></param>
        public static void ModifyDevMap(BTSChlMap bts)
        {
            string sql = "insert into bts_chl_map(dev_ip,cabinet_id,mid_id,chl_map_id,real_chl_id,revo_stat,own_aux) values(@ip,@gid,@mid,@cid,@rid,@revo,@aux)";
            try
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", bts.DevIp));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", bts.Gid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", bts.Mid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", bts.ChlMapId));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@rid", bts.ChlRealId));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@revo", bts.RevocationStatus));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@aux", bts.StrAuxNums));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
        }
        /// <summary>
        /// 查询是否有辅助通道映射
        /// </summary>
        /// <param name="bts"></param>
        /// <returns></returns>
        public static bool queryMap(BTSChlMap bts)
        {
            try
            {
                string sqlStr = "select * from bts_chl_map where dev_ip=@ip and cabinet_id=@gid and mid_id=@mid and chl_map_id=@cid and real_chl_id=@rid";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", bts.DevIp));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", bts.Gid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", bts.Mid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", bts.ChlMapId));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@rid", bts.ChlRealId));
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
                return false;
            }
        }
        /// <summary>
        /// 添加辅助通道映射信息
        /// </summary>
        /// <param name="bts"></param>
        public static void ModifyAuxMap(BTSChlMap bts)
        {
            string sql = "update bts_chl_map set aux_num=@aux where dev_ip=@ip and cabinet_id=@gid and mid_id=@mid and chl_map_id=@cid and real_chl_id=@rid";
            try
            {
                using (DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sql))
                {
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@aux", bts.StrAuxNums));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", bts.DevIp));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", bts.Gid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", bts.Mid));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@cid", bts.ChlMapId));
                    cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@rid", bts.ChlRealId));
                    DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
        }

        /// <summary>
        /// 删除设备映射信息
        /// </summary>
        /// <param name="devId"></param>
        public static void DeleteDevMapById(string ip,int gid,int mid)
        {
            try
            {
                string sqlStr = "delete from bts_chl_map where dev_ip = @ip and cabinet_id=@gid and mid_id=@mid";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                DBAccessManager.SqlDBAccess.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
        }
        /// <summary>
        /// 查询映射表的行列
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public static List<int> QueryMapRowAndCol(string ip,int gid,int mid)
        {
            List<int> rowAndCol = new List<int>();
            int row = 0;
            int col = 0;
            try
            {
                string sqlStr = "select count(*) from bts_chl_map where dev_ip=@ip and cabinet_id=@gid and mid_id=@mid;select count(*) from bts_chl_map where dev_ip=@ip";
                DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, sqlStr);
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                var ds = DBAccessManager.SqlDBAccess.Fill(cmd);
                col = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                row = col==0?0:Convert.ToInt32(ds.Tables[1].Rows[0][0]) / col;
                rowAndCol.Add(col);
                rowAndCol.Add(row);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return rowAndCol;
        }
        /// <summary>
        /// 查询撤销通道号
        /// </summary>
        /// <param name="ip"></param>
        /// <returns></returns>
        public static List<int> QueryRevokeChl(string ip,int gid,int mid)
        {
            List<int> revoIds = new List<int>();
            string str = "select real_chl_id from bts_chl_map where revo_stat=@revo and dev_ip = @ip and cabinet_id=@gid and mid_id=@mid order by real_chl_id";
            try
            {
                using (DbCommand dbcmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, str))
                {
                    dbcmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@revo", 1));
                    dbcmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
                    dbcmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
                    dbcmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
                    DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(dbcmd);
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            revoIds.Add(Convert.ToInt32(reader["real_chl_id"]));
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return revoIds;
        }
        /// <summary>
        /// 查询未被撤销的通道号集合
        /// </summary>
        /// <param name="ip"></param>
        /// <returns></returns>
        public static List<int> ChannelNum(string ip,int gid,int mid)
        {
            List<int> chlList = new List<int>();
            string str = "select real_chl_id from bts_chl_map where revo_stat=@revo_stat and dev_ip=@ip and cabinet_id=@gid and mid_id=@mid order by real_chl_id";
            DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, str);
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@revo_stat", 0));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
            try
            {
                using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            chlList.Add(Convert.ToInt32(reader["real_chl_id"]));
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
            return chlList;
        }
        /// <summary>
        /// 查询辅助通道映射信息
        /// </summary>
        /// <returns></returns>
        public static Dictionary<int,string> queryOwnAux(string ip,int gid,int mid)
        {
            Dictionary<int, string> auxDic = new Dictionary<int, string>();
            string str = "select chl_map_id,own_aux from bts_chl_map where revo_stat=@revo_stat and dev_ip=@ip and cabinet_id=@gid and mid_id=@mid";
            DbCommand cmd = DBAccessManager.SqlDBAccess.CreateCommand(CommandType.Text, str);
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@revo_stat", 0));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@ip", ip));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@gid", gid));
            cmd.Parameters.Add(DBAccessManager.SqlDBAccess.CreateParameter("@mid", mid));
            using (DbDataReader reader = DBAccessManager.SqlDBAccess.ExecuteReader(cmd))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        auxDic.Add(Convert.ToInt32(reader["chl_map_id"]), Convert.ToString(reader["own_aux"]));
                    }
                }
            }
            return auxDic;
        }
    }
}
